package com.yyy.dao;

import com.yyy.entity.Category;
import com.yyy.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author：yyy
 * @Package：com.yyy.dao
 * @Project：HuTuBiill
 * @name：CategoryDao
 * @Date：2024/11/10 22:03
 * @Filename：CategoryDao
 * Category表持久层
 */
public class CategoryDao {



    /*
    * 添加
    * */
    public void add(Category category){
        String sql="insert into category values(null,?)";
        try {
            Connection connection= DBUtil.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,category.name);
            ps.execute();
            ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                int id=rs.getInt(1);
                category.id=id;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /*
    * 根据id删除
    * */
    public void delete(int id){
        try {
            Connection connection= DBUtil.getConnection();
            Statement s= connection.createStatement();
            String sql="delete from category where id ="+id;
            s.execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /*
    *
    * 根据id更新
    * */
    public void update(Category category){
        String sql="update category set name=? where id=?";
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setString(1,category.name);
            ps.setInt(2,category.id);
            ps.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /*
    *
    * 根据id查询
    * */
    public Category selectByid(int id){
        Category category=null;
        try {
            String sql="select * from category  where id="+id;
            Connection connection=DBUtil.getConnection();
            Statement ps= connection.createStatement();
            ResultSet resultSet = ps.executeQuery(sql);
            if(resultSet.next()){
                category=new Category();
                category.name=resultSet.getString(2);
                category.id=id;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return category;
    }

    /*
    * 分页查询
    * */
    public List<Category> list(int start,int count){
        List<Category> categories=new ArrayList<>();
        String sql="select * from category order by id desc limit ?,?";
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setInt(1,start);
            ps.setInt(2,count);
            ResultSet rs= ps.executeQuery();
            while (rs.next()){
                Category category=new Category();
                int id=rs.getInt(1);
                String name=rs.getString(2);
                category.id=id;
                category.name=name;
                categories.add(category);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return categories;
    }

    /*
    *
    * 查询全部
    * */

    public List<Category> list(){
        return list(0,Short.MAX_VALUE);
    }

    /*
    *
    * 查询总数
    * */
    public int getTotal(){
        int total=0;
        try {
            String sql="select count(1) from category" ;
            Connection connection=DBUtil.getConnection();
            Statement ps= connection.createStatement();
            ResultSet resultSet = ps.executeQuery(sql);
            while (resultSet.next()){
                total=resultSet.getInt(1);
            }
            System.out.println("total:"+total);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return total;
    }

























}
